using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for MenuBll
/// </summary>
public class MenuBll
{
	public MenuBll()
	{	
	}

    private const string SQL_SELECT_MENU = "SELECT * FROM LEFTMENU ORDER BY ID ASC";
    private const string SQL_SELECT_MENU_ITEM = "SELECT * FROM LEFTMENU WHERE PARENT = @PARENT";
    private const string SQL_SELECT_MENU_BYID = "SELECT * FROM LEFTMENU WHERE ID = @ID";
    private const string SQL_DELETE_MENU = "DELETE FROM LEFTMENU WHERE ID = @ID";
    private const string SQL_INSERT_MENU = "INSERT INTO LEFTMENU(TITLE, ADDRESS, PARENT) VALUES (@TITLE, @ADDRESS, @PARENT)";
    private const string SQL_UPDATE_MENU = "UPDATE LEFTMENU SET TITLE = @TITLE, ADDRESS = @ADDRESS WHERE ID = @ID";
    private const string PARM_ID = "@ID";
    private const string PARM_TITLE = "@TITLE";
    private const string PARM_ADDRESS = "@ADDRESS";
    private const string PARM_PARENT = "@PARENT";	

    private Menu MenuManipulate(SqlDataReader reader)
    {
        Menu m = new Menu();
        if (!reader.IsDBNull(0))
            m.Id = reader.GetInt32(0);
        if (!reader.IsDBNull(1))
            m.Title = reader.GetString(1);
        if (!reader.IsDBNull(2))
            m.Address = reader.GetString(2);
        if (!reader.IsDBNull(3))
            m.Parent = reader.GetInt32(3);
        return m;
    }

    public IList<Menu> GetAll()
    {
        IList<Menu> result = new List<Menu>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_MENU, null))
        {
            while (reader.Read())
            {
                Menu m = MenuManipulate(reader);
                result.Add(m);
            }
        }
        return result;
    }

    public Menu GetMenuByID(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_ID, SqlDbType.Int) };
        parms[0].Value = id;

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_MENU_BYID, parms))
        {
            if (reader.Read())
            {
                Menu m = MenuManipulate(reader);
                return m;
            }
        }
        return null;
    }

    public IList<Menu> GetMenuItem(int parent)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_PARENT, SqlDbType.Int) };
        parms[0].Value = parent;

        IList<Menu> result = new List<Menu>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_MENU_ITEM, parms))
        {
            while (reader.Read())
            {
                Menu m = MenuManipulate(reader);
                result.Add(m);
            }
        }
        return result;
    }
    
    public bool Delete(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_ID, SqlDbType.Int) };
        parms[0].Value = id;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_DELETE_MENU, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Update(Menu m)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {            
            new SqlParameter(PARM_ID, SqlDbType.Int),
            new SqlParameter(PARM_TITLE, SqlDbType.NVarChar),
            new SqlParameter(PARM_ADDRESS, SqlDbType.NChar)};
        parms[0].Value = m.Id;     
        parms[1].Value = m.Title;
        parms[2].Value = m.Address;
        
        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_UPDATE_MENU, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Insert(Menu m)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_TITLE, SqlDbType.NVarChar),
            new SqlParameter(PARM_ADDRESS, SqlDbType.VarChar),
            new SqlParameter(PARM_PARENT, SqlDbType.Int)};
        parms[0].Value = m.Title;
        parms[1].Value = m.Address;
        parms[2].Value = m.Parent;

        try
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            result = SQLDBHelper.ExecuteNonQuery(conn,
                CommandType.Text, SQL_INSERT_MENU, parms);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }
}
